package com.pxl.springbootinit.mapper;

import com.pxl.springbootinit.model.entity.Item;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.pxl.springbootinit.model.vo.RankVO;
import com.pxl.springbootinit.model.vo.StatisticsItemTypeVO;
import io.swagger.models.auth.In;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author MADAO
* @description 针对表【item(物品信息表)】的数据库操作Mapper
* @createDate 2024-12-20 12:02:23
* @Entity com.pxl.springbootinit.model.entity.Item
*/
public interface ItemMapper extends BaseMapper<Item> {

    @Select("SELECT category from item where status ='APPROVED' and is_deleted = 0 GROUP BY category;")
    List<String> selectTypes();

    @Select("SELECT category from item where is_deleted = 0 GROUP BY category;")
    List<String> selectAllTypes();

    @Select("SELECT user_id, SUM(user_id) AS donationAmount FROM item where is_deleted = 0 GROUP BY user_id ORDER BY donationAmount DESC limit #{nums};")
    List<RankVO> rank(Integer nums);

    @Select("SELECT count(*) as totalDonation" +
            " FROM item" +
            " where is_deleted = 0;")
    Integer totalDonation();

    @Select("SELECT COUNT(*) AS todayDonation" +
            " FROM item" +
            " WHERE is_deleted = 0 AND created_time >= CURDATE() AND created_time < CURDATE() + INTERVAL 1 DAY;")
    Integer todayDonation();

    @Select("SELECT COUNT(DISTINCT user_id) AS totalDonor " +
            "            FROM item"  +
            "            WHERE is_deleted = 0;")
    Integer totalDonor();

    @Select("SELECT " +
            "    COALESCE(SUM(CASE WHEN DATE(i.created_time) = DATE(NOW() - INTERVAL 6 DAY) + INTERVAL a.a DAY THEN 1 ELSE 0 END), 0) AS new_items_count" +
            " FROM " +
            "    (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) AS a" +
            " LEFT JOIN `item` i " +
            "    ON DATE(i.created_time) = DATE(NOW() - INTERVAL 6 DAY) + INTERVAL a.a DAY" +
            "    AND i.is_deleted = 0 " +
            "GROUP BY a.a;")
    List<Integer> getRecent7DayAddCount();


    @Select("SELECT " +
            "    category as `name`, " +
            "    COUNT(*) AS `value`" +
            "FROM " +
            "    `item` " +
            "WHERE " +
            "    is_deleted = 0  " +
            "GROUP BY " +
            "    category " +
            "ORDER BY " +
            "    `value` DESC;")
    List<StatisticsItemTypeVO> getItemGroupType();
}




